﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class GlobalParamDAL
    {
        private const string SQL_SELECT_WS_GLOBAL_PARAMS = "select GOP_PARAM_KEY,GOP_PARAM_VALUE from WS_GLOBAL_PARAMS where 1=1";
        private const string SQL_INSERT_WS_GLOBAL_PARAMS = "insert into WS_GLOBAL_PARAMS(GOP_PARAM_KEY,GOP_PARAM_VALUE) values(@ParamKey,@ParamValue)";
        private const string SQL_UPDATE_WS_GLOBAL_PARAMS = "update WS_GLOBAL_PARAMS set GOP_PARAM_VALUE=@ParamValue where 1=1";
        private const string SQL_SELECT_SYSTEM_TIME = "select date_format(SYSDATE(),'%Y-%m-%d %H:%i:%s')";

        private const string SQL_WHERE_PARAM_KEY = " and GOP_PARAM_KEY=@ParamKey ";

        private const string PARM_PARAM_KEY = "@ParamKey";
        private const string PARM_PARAM_VALUE = "@ParamValue";

        private static GlobalParamDAL instance = new GlobalParamDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static GlobalParamDAL Instance
        {
            get
            {
                return instance;
            }
        }
        /// <summary>
        /// 获取记录
        /// </summary>
        /// <param name="key">键值</param>
        /// <returns></returns>
        public GlobalParamModel Get(string key)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_GLOBAL_PARAMS);
            sbSQL.Append(SQL_WHERE_PARAM_KEY);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_PARAM_KEY, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = key });

            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray()))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow t = ds.Tables[0].Rows[0];
                    return new GlobalParamModel()
                    {
                        Key = Convert.ToString(t.ItemArray[0]),
                        Value = Convert.ToString(t.ItemArray[1])
                    };
                }
                return null;
            }
        }
        /// <summary>
        /// 更新记录
        /// </summary>
        /// <param name="trans">数据库事务对象</param>
        /// <param name="model">模型对象</param>
        /// <returns></returns>
        public int Update(DbTransaction trans, GlobalParamModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_UPDATE_WS_GLOBAL_PARAMS);
            sbSQL.Append(SQL_WHERE_PARAM_KEY);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_PARAM_KEY, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Key });
            parms.Add(new MySqlParameter(PARM_PARAM_VALUE, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Key });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        /// <summary>
        /// 插入记录
        /// </summary>
        /// <param name="trans">数据库事务对象param>
        /// <param name="model">模型对象</param>
        /// <returns></returns>
        public int Insert(DbTransaction trans, GlobalParamModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_PARAM_KEY, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Key });
            parms.Add(new MySqlParameter(PARM_PARAM_VALUE, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Value });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_GLOBAL_PARAMS, parms.ToArray());
        }
        public DateTime GetSystemTime()
        {
            using (DataSet ds = MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, SQL_SELECT_SYSTEM_TIME, null))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow t = ds.Tables[0].Rows[0];
                    return Convert.ToDateTime(t.ItemArray[0]);
                }
                return DateTime.Now;
            }
        }
    }
}
